//------------------------------------------------------------------------------
// <copyright company="Telligent Systems">
//     Copyright (c) Telligent Systems Corporation.  All rights reserved.
// </copyright> 
//------------------------------------------------------------------------------

using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using CommunityServer.Components;
using CommunityServer.Discussions.Components;
using CommunityServer.SqlDataProvider;

namespace CommunityServer.Data
{
	/// <summary>
	/// Summary description for ForumsSqlDataProvider.
	/// </summary>
	public class ForumsSqlDataProvider : ForumDataProvider
	{
        #region SettingsID 
        protected int GetSettingsID()
        {
            return  sqlHelper.GetSettingsID();
        }

        protected SqlParameter SettingsIDParameter()
        {
            SqlParameter p = new SqlParameter("@SettingsID",SqlDbType.Int);
            p.Value = GetSettingsID();
            return p;
        }
        #endregion

        #region Member variables
        protected string databaseOwner	= "dbo";	// overwrite in web.config
        string connectionString = null;
        ProviderHelper sqlHelper = null;
        #endregion

        #region Constructor
        public ForumsSqlDataProvider(string databaseOwner, string connectionString) 
        {

            // Read the connection string for this provider
            //
            this.connectionString = connectionString;

            // Read the database owner name for this provider
            //
            this.databaseOwner = databaseOwner;

            sqlHelper = ProviderHelper.Instance();

        }
        #endregion

        #region helpers
        protected SqlConnection GetSqlConnection () 
        {

            try 
            {
                return new SqlConnection(ConnectionString);
            } 
            catch 
            {
                throw new CSException(CSExceptionType.DataProvider, "SQL Connection String is invalid.");
            }

        }

        public string ConnectionString 
        {
            get 
            {
                return connectionString;
            }
            set 
            {
                connectionString = value;
            }
        }
        #endregion
        
        #region GetPosts
        /// <summary>
        /// Returns a collection of Posts that make up a particular thread with paging
        /// </summary>
        /// <param name="postID">The ID of a Post in the thread that you are interested in retrieving.</param>
        /// <returns>A PostCollection object that contains the posts in the thread.</returns>
        /// 
        public override PostSet GetPosts(int postID, int pageIndex, int pageSize, int sortBy, int sortOrder, int userID, bool returnRecordCount, bool includeCategories) 
        {

            // Create Instance of Connection and Command Object
            //
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_forums_Posts_PostSet", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;
                PostSet postSet = new PostSet();

                // Set parameters
                //
                myCommand.Parameters.Add("@PostID", SqlDbType.Int).Value            = postID;
                myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value         = pageIndex;
                myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value          = pageSize;
                myCommand.Parameters.Add("@SortBy", SqlDbType.Int).Value            = sortBy;
                myCommand.Parameters.Add("@SortOrder", SqlDbType.Int).Value         = sortOrder;
                myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value            = userID;
                myCommand.Parameters.Add("@ReturnRecordCount", SqlDbType.Bit).Value = returnRecordCount;
				myCommand.Parameters.Add("@IncludeCategories", SqlDbType.Bit).Value = includeCategories;
                myCommand.Parameters.Add(this.SettingsIDParameter());

                // Execute the command
                //
                myConnection.Open();
                using(SqlDataReader reader = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {

                    // Get the results
                    //
                    while (reader.Read())
                        postSet.Posts.Add( PopulatePostFromIDataReader(reader) );

					if (includeCategories)
					{
						reader.NextResult();

						Hashtable categoryLookupTable = new Hashtable();

						while(reader.Read()) 
						{
							int cPostID = (int) reader["PostID"];

							if (categoryLookupTable[cPostID] == null) 
							{
								categoryLookupTable[cPostID] = new ArrayList();
							}

							((ArrayList) categoryLookupTable[cPostID]).Add(reader["Name"]);
						}

						// Map categories to the threads
						//
						foreach (ForumPost post in postSet.Posts) 
						{
							if (categoryLookupTable.ContainsKey(post.PostID))
								post.Categories = (string[]) ((ArrayList) categoryLookupTable[post.PostID]).ToArray(typeof(string));
						}
					}

                    // Are we expecting more results?
                    //
                    if ((returnRecordCount) && (reader.NextResult()) ) 
                    {
                        reader.Read();

                        // Read the value
                        //
                        postSet.TotalRecords = (int) reader[0];
                    }
                    reader.Close();
                }

                myConnection.Close();

                return postSet;
            }



        }


		/// <summary>
		/// Returns the most recent X number of posts from ALL threads for a RSS feed
		/// </summary>
		/// <returns>A PostCollection object that contains the posts in the thread.</returns>
		/// 
		public override PostSet GetPosts_RSSThreadless(int sectionID, int pageSize) 
		{

			// Create Instance of Connection and Command Object
			//
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_forums_Posts_PostSet_RSSThreadless", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;
				PostSet postSet = new PostSet();

				// Set parameters
				//
				myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
				myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = sectionID;
				myCommand.Parameters.Add(this.SettingsIDParameter());

				// Execute the command
				//
				myConnection.Open();
				using(SqlDataReader reader = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
				{

					// Get the results
					//
					while (reader.Read())
						postSet.Posts.Add( PopulatePostFromIDataReader(reader) );

					reader.Close();
				}

				myConnection.Close();

				return postSet;
			}



		}
        #endregion

        #region #### Post ####

        /// <summary>
        /// Get basic information about a single post.  This method returns an instance of the Post class,
        /// which contains less information than the PostDeails class, which is what is returned by the
        /// GetPostDetails method.
        /// </summary>
        /// <param name="postID">The ID of the post whose information we are interested in.</param>
        /// <returns>An instance of the Post class.</returns>
        /// <remarks>If a PostID is passed in that is NOT found in the database, a PostNotFoundException
        /// exception is thrown.</remarks>
        public override ForumPost GetPost(int postID, int userID, bool trackViews, bool markRead, bool includeCategories) 
        {

            // Create Instance of Connection and Command Object
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_forums_Post", myConnection);

                // Mark the Command as a SPROC
                myCommand.CommandType = CommandType.StoredProcedure;

                // Add Parameters to SPROC
                myCommand.Parameters.Add("@PostID", SqlDbType.Int).Value = postID;
                myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;
                myCommand.Parameters.Add("@TrackViews", SqlDbType.Bit).Value = trackViews;
                myCommand.Parameters.Add(this.SettingsIDParameter());
                myCommand.Parameters.Add("@MarkRead", SqlDbType.Bit).Value = markRead;
				myCommand.Parameters.Add("@IncludeCategories", SqlDbType.Bit).Value = includeCategories;

                // Execute the command
                myConnection.Open();
                ForumPost p = null;
                using(SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {

                    if (!dr.Read()) 
                    {
                        dr.Close();
                        myConnection.Close();
                        // we did not get back a post
                        throw new CSException(CSExceptionType.PostNotFound, postID.ToString());
                    }

                    p = PopulatePostFromIDataReader(dr);

					if (includeCategories)
					{
						ArrayList categories = new ArrayList();

						dr.NextResult();
						while (dr.Read())
						{
							categories.Add(dr["Name"]);
						}

						p.Categories = (string[]) categories.ToArray(typeof(string));
					}

                    dr.Close();
                }
                myConnection.Close();


                // we have a post to work with  
                return p;
            }
        }

		public override void UpdatePostCategories(int sectionID, int postID, string[] categoryList)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Posts_UpdatePostsInCategories", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add parameters
				//
				myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = sectionID;
				myCommand.Parameters.Add("@PostID", SqlDbType.Int).Value = postID;
				myCommand.Parameters.Add("@CategoryList", SqlDbType.NVarChar, 4000).Value = ConvertCategoriesToXML(categoryList);
				myCommand.Parameters.Add(this.SettingsIDParameter());
				// Execute the command
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				myConnection.Close();
			}
		}

		public override ArrayList GetPostCategories(int postID)
		{
			// Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Posts_GetPostInCategories", myConnection);
				ArrayList categories = new ArrayList();

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@PostID", SqlDbType.Int).Value = postID;

				// Execute the command
				myConnection.Open();
				SqlDataReader dr = myCommand.ExecuteReader();

				while(dr.Read())
				{
					// Add categories
					PostCategory category = new PostCategory();

					category.CategoryID =			(int) dr["CategoryID"];
					category.SectionID =				(int) dr["SectionID"];
					category.Name =					(string) dr["Name"];
					category.IsEnabled =			(bool) dr["IsEnabled"];
					category.ParentID =				(int) dr["ParentID"];

					if(dr["Description"] != DBNull.Value)
						category.Description =		(string) dr["Description"];

					categories.Add(category);
				}

				dr.Close();
				myConnection.Close();

				// return the collection of IDs
				return categories;
			}
		}

        #endregion

        #region Add/Update Post
        /// <summary>
        /// Adds a new Post.  This method checks the allowDuplicatePosts settings to determine whether
        /// or not to allow for duplicate posts.  If allowDuplicatePosts is set to false and the user
        /// attempts to enter a duplicate post, a PostDuplicateException exception is thrown.
        /// </summary>
        /// <param name="post">A Post object containing the information needed to add a new
        /// post.  The essential fields of the Post class that must be set are: the Subject, the
        /// Body, the Username, and a ForumID or a ParentID (depending on whether the post to add is
        /// a new post or a reply to an existing post, respectively).</param>
        /// <returns>A Post object with information on the newly inserted post.  This returned Post
        /// object includes the ID of the newly added Post (PostID) as well as if the Post is
        /// Approved or not.</returns>
        public override ForumPost AddPost (ForumPost post, int userID)
        {
            int postID = -1;

            // Create Instance of Connection and Command Object
            //
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Post_CreateUpdate", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                // Add parameters
                //
                myCommand.Parameters.Add(this.SettingsIDParameter());
                myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = post.SectionID;
                myCommand.Parameters.Add("@ParentID", SqlDbType.Int).Value = post.ParentID;
                myCommand.Parameters.Add("@AllowDuplicatePosts", SqlDbType.Bit).Value = ForumConfiguration.Instance().EnableDuplicatePosts;
                myCommand.Parameters.Add("@DuplicateIntervalInMinutes", SqlDbType.Int).Value = ForumConfiguration.Instance().DuplicatePostIntervalInMinutes;
                myCommand.Parameters.Add("@Subject", SqlDbType.NVarChar, 256).Value = post.Subject;
                myCommand.Parameters.Add("@IsLocked", SqlDbType.Bit).Value = post.IsLocked;
                myCommand.Parameters.Add("@IsTracked", SqlDbType.Bit).Value = post.IsTracked;
                myCommand.Parameters.Add("@PostType", SqlDbType.Int).Value = post.PostType;
				myCommand.Parameters.Add("@PostMedia", SqlDbType.Int).Value = post.PostMedia;
				myCommand.Parameters.Add("@EmoticonID", SqlDbType.Int).Value = post.EmoticonID;
                myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;
                myCommand.Parameters.Add("@Body", SqlDbType.NText).Value = post.Body;
                myCommand.Parameters.Add("@FormattedBody", SqlDbType.NText).Value = post.FormattedBody;
                myCommand.Parameters.Add("@UserHostAddress", SqlDbType.NVarChar, 32).Value = post.UserHostAddress;
                myCommand.Parameters.Add("@PostID", SqlDbType.Int).Direction = ParameterDirection.Output;
                myCommand.Parameters.Add("@PostName", SqlDbType.NVarChar,256).Value = post.Name;
                myCommand.Parameters.Add("@ApplicationPostType", SqlDbType.Int,4).Value = post.ForumPostType;
				myCommand.Parameters.Add("@Points", SqlDbType.Int).Value = post.Points;
                myCommand.Parameters.Add("@PostDisplayName",SqlDbType.NVarChar,64).Value = post.User.DisplayName;
				myCommand.Parameters.Add("@PostAuthor", SqlDbType.NVarChar, 128).Value = post.Username;

                if (post is Thread) 
                {
                    myCommand.Parameters.Add("@IsSticky", SqlDbType.Bit).Value = ((Thread) post).IsSticky;
                    myCommand.Parameters.Add("@StickyDate", SqlDbType.DateTime).Value = ((Thread) post).StickyDate;
                    myCommand.Parameters.Add("@ThreadStatus", SqlDbType.Int).Value = ((Thread) post).Status;
                }

                SerializerData data = post.GetSerializerData();

                myCommand.Parameters.Add("@PropertyNames", SqlDbType.NText).Value = data.Keys;
                myCommand.Parameters.Add("@PropertyValues", SqlDbType.NText).Value = data.Values;

				myCommand.Parameters.Add("@IsApproved", SqlDbType.Bit).Value = post.IsApproved;
				myCommand.Parameters.Add("@AutoApprove", SqlDbType.Bit).Value = post.AutoApprove;

                myCommand.Parameters.Add("@PostConfiguration", SqlDbType.Int).Value = post.PostConfiguration;

                myConnection.Open();
                myCommand.ExecuteNonQuery();
                myConnection.Close();

                // LN 5/27/04: try/catch added to get rid of exceptions
                try 
                {
                    postID = (int) myCommand.Parameters["@PostID"].Value;
                } 
                catch {}

                if (postID == -1) 
                {
                    throw new CSException(CSExceptionType.PostDuplicate);
                }
                            
                // Return the newly inserted Post
                //
                return GetPost( postID, userID, false, false, false );
            }
        }
        

        /// <summary>
        /// Updates a post.
        /// </summary>
        /// <param name="post">The Post data used to update the Post.  The ID of the UpdatedPost
        /// Post object corresponds to what post is to be updated.  The only other fields used to update
        /// the Post are the Subject and Body.</param>
        public override void UpdatePost(ForumPost post, int editedBy) 
        {
            // Create Instance of Connection and Command Object
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Post_Update", myConnection);

                // Mark the Command as a SPROC
                myCommand.CommandType = CommandType.StoredProcedure;

                // Add Parameters to SPROC
                myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = post.SectionID;
                myCommand.Parameters.Add("@PostID", SqlDbType.Int, 4).Value = post.PostID;
                myCommand.Parameters.Add("@Subject", SqlDbType.NVarChar, 256).Value = post.Subject;
                myCommand.Parameters.Add("@Body", SqlDbType.NText).Value = post.Body;
                myCommand.Parameters.Add("@FormattedBody", SqlDbType.NText).Value = post.FormattedBody;
                myCommand.Parameters.Add("@EmoticonID", SqlDbType.Int).Value = post.EmoticonID;
                myCommand.Parameters.Add("@IsLocked", SqlDbType.Bit).Value = post.IsLocked;
				myCommand.Parameters.Add("@Points", SqlDbType.Int).Value = post.Points;
				myCommand.Parameters.Add("@PostMedia", SqlDbType.Int).Value = post.PostMedia;
				myCommand.Parameters.Add("@PostStatus", SqlDbType.Int).Value = post.PostStatus;
				myCommand.Parameters.Add("@SpamScore", SqlDbType.Int).Value = post.SpamScore;

				if (post is IThread)
					myCommand.Parameters.Add("@IsAnnouncement", SqlDbType.Bit).Value = ((IThread) post).IsAnnouncement;
				else
					myCommand.Parameters.Add("@IsAnnouncement", SqlDbType.Bit).Value = false;

                myCommand.Parameters.Add("@EditedBy", SqlDbType.Int).Value = editedBy;
                myCommand.Parameters.Add("@EditNotes", SqlDbType.NText).Value = post.EditNotes;
                myCommand.Parameters.Add(this.SettingsIDParameter());

                // Allow Thread to update sticky properties.
                //
                if (post.ParentID == 0) 
                {
                    Thread thread = (Thread) post;
                    myCommand.Parameters.Add("@IsSticky", SqlDbType.Bit).Value = thread.IsSticky;
                    myCommand.Parameters.Add("@StickyDate", SqlDbType.DateTime).Value = thread.StickyDate;
                }

                SerializerData data = post.GetSerializerData();

                myCommand.Parameters.Add("@PropertyNames", SqlDbType.NText).Value = data.Keys;
                myCommand.Parameters.Add("@PropertyValues", SqlDbType.NText).Value = data.Values;
                myCommand.Parameters.Add("@PostConfiguration", SqlDbType.Int).Value = post.PostConfiguration;

                // Execute the command
                myConnection.Open();
                myCommand.ExecuteNonQuery();
                myConnection.Close();
            }
        }

        #endregion

        #region Threads 

        /****************************************************************
        // GetNextThreadID
        //
        /// <summary>
        /// Gets the next threadid based on the postid
        /// </summary>
        // 
        ****************************************************************/
        public override int GetNextThreadID(int postID) 
        {

            // Create Instance of Connection and Command Object
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Thread_PrevNext", myConnection);
                SqlDataReader reader;
                int threadID = postID;

                // Mark the Command as a SPROC
                myCommand.CommandType = CommandType.StoredProcedure;

                // Add Parameters to SPROC
                myCommand.Parameters.Add("@PostID", SqlDbType.Int).Value = postID;
				myCommand.Parameters.Add("@NavigationType", SqlDbType.Int).Value = ThreadNavigationType.Next;
				myCommand.Parameters.Add(this.SettingsIDParameter());

                // Execute the command
                myConnection.Open();

                using(reader = myCommand.ExecuteReader(CommandBehavior.CloseConnection|CommandBehavior.SingleResult))
                {
                    while (reader.Read())
                        threadID = (int) reader["ThreadID"];

                    reader.Close();
                }
                myConnection.Close();

                return threadID;
            }
        }

        /****************************************************************
        // GetPrevThreadID
        //
        /// <summary>
        /// Gets the prev threadid based on the postid
        /// </summary>
        //
        ****************************************************************/
        public override int GetPrevThreadID(int postID) 
        {
            // Create Instance of Connection and Command Object
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Thread_PrevNext", myConnection);
                SqlDataReader reader;
                int threadID = postID;

                // Mark the Command as a SPROC
                myCommand.CommandType = CommandType.StoredProcedure;

                // Add Parameters to SPROC
                myCommand.Parameters.Add("@PostID", SqlDbType.Int).Value = postID;
				myCommand.Parameters.Add("@NavigationType", SqlDbType.Int).Value = ThreadNavigationType.Previous;
                myCommand.Parameters.Add(this.SettingsIDParameter());

                // Execute the command
                myConnection.Open();

                using(reader = myCommand.ExecuteReader(CommandBehavior.CloseConnection|CommandBehavior.SingleResult))
                {

                    while (reader.Read())
                        threadID = (int) reader["ThreadID"];

                    reader.Close();
                }
                myConnection.Close();

                return threadID;
            }
        }

        public override void UpdateThreadStatus (int threadID, ThreadStatus status) {
            // Create Instance of Connection and Command Object
            using (SqlConnection myConnection = GetSqlConnection()) {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Thread_Status_Update", myConnection);

                // Mark the Command as a SPROC
                myCommand.CommandType = CommandType.StoredProcedure;

                myCommand.Parameters.Add( "@ThreadID", SqlDbType.Int ).Value = threadID;
                myCommand.Parameters.Add( "@Status", SqlDbType.Int ).Value = (int) status;
                myCommand.Parameters.Add( SettingsIDParameter() );

                // Execute the command
                myConnection.Open();
                myCommand.ExecuteNonQuery();
                myConnection.Close();
            }
        }
        
        /// <summary>
        /// Permanently deletes a thread and its related data.
        /// </summary>
        /// <param name="sectionID">Forum ID.</param>
        /// <param name="threadID">Thread ID.</param>
        /// <param name="resetStatistics">Tells if section's statistics should be updated this time.</param>
        public override void DeleteThread (int sectionID, int threadID, bool resetStatistics)
        {
            // Create Instance of Connection and Command Object
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Thread_Delete", myConnection);

                // Mark the Command as a SPROC
                myCommand.CommandType = CommandType.StoredProcedure;

                // Add Parameters to SPROC
                myCommand.Parameters.Add("@SectionID", SqlDbType.Int, 4).Value = sectionID;
                myCommand.Parameters.Add("@ThreadID", SqlDbType.Int, 4).Value = threadID;
                myCommand.Parameters.Add(this.SettingsIDParameter());
                myCommand.Parameters.Add("@ResetStatistics", SqlDbType.Bit).Value = resetStatistics;
                myCommand.Parameters.Add("@DeletedBy", SqlDbType.Int).Value = CSContext.Current.User.UserID;
                myCommand.Parameters.Add("@Reason", SqlDbType.NVarChar, 1024).Value = "";

                // Execute the command
                myConnection.Open();
                myCommand.ExecuteNonQuery();
                myConnection.Close();
            }
        }

        #endregion

        #region #### Threads ####

        #region Public Static Helper Functions
        public static DateTime GetSafeSqlDateTime (DateTime date) 
        {

            if (date == DateTime.MinValue)
                return (DateTime) System.Data.SqlTypes.SqlDateTime.MinValue;
            return date;

        }
        #endregion

		/// <summary>
		/// Get basic information about a single thread.  This method returns an instance of the Post class,
		/// which contains less information than the PostDeails class, which is what is returned by the
		/// GetPostDetails method.
		/// </summary>
		/// <param name="threadID">The ID of the thread whose information we are interested in.</param>
		/// <returns>An instance of the Post class.</returns>
		/// <remarks>If a ThreadID is passed in that is NOT found in the database, null is returned.</remarks>
		public override Thread GetThread(int threadID, int userID)
		{

			// Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_forums_Thread_Get", myConnection);

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@ThreadID", SqlDbType.Int).Value = threadID;
				myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;
				myCommand.Parameters.Add(this.SettingsIDParameter());

				// Execute the command
				myConnection.Open();
                Thread thread = null;
                using(SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection|CommandBehavior.SingleRow))
                {
                    if(dr.Read())
                    thread = PopulateThreadFromIDataReader(dr);

                    dr.Close();
                }
				myConnection.Close();


				// we have a post to work with  
				return thread;
			}
		}

        
		public override ThreadSet GetThreads(ForumThreadQuery query)
		{

			// Create Instance of Connection and Command Object
			//
			using( SqlConnection connection = GetSqlConnection() ) 
			{
				User user = CSContext.Current.User;
				SqlCommand command = new SqlCommand(databaseOwner + ".cs_Threads_GetThreadSet", connection);
				command.CommandType = CommandType.StoredProcedure;
				bool myForums = false;

				ThreadSet threadSet             = new ThreadSet();
				StringBuilder sqlCountSelect    = new StringBuilder("SELECT count(T.ThreadID) ");      
				StringBuilder sqlPopulateSelect = new StringBuilder("SELECT T.ThreadID ");
				StringBuilder fromClause        = new StringBuilder(" FROM " + this.databaseOwner + ".cs_Threads T ");
				StringBuilder whereClause       = new StringBuilder(" WHERE ");
				StringBuilder orderClause       = new StringBuilder(" ORDER BY ");

				// Ensure DateTime is min value for SQL
				//
				query.ThreadsNewerThan = ProviderHelper.GetSafeSqlDateTime(query.ThreadsNewerThan);

				// Construct the clauses
				#region Constrain Forums

				whereClause.AppendFormat(" T.SettingsID = {0} ",this.GetSettingsID());

				// Contrain the selectivness to a set of specified forums. The ForumID is our
				// clustered index so we want this to be first
				if (query.ForumID > 0) 
				{
					whereClause.Append(" AND T.SectionID = ");
					whereClause.Append(query.ForumID);
				} 
				else if (query.ForumID < 0) 
				{
					// This is a request for My Forums threads
					//
					myForums = true;

					// Get a list of all the forums the user has access to
					//
					ArrayList forumList = Forums.GetForums(false, true, false, false );

					// Remove the private messaging forum, so our next check step is better
					for(int i = forumList.Count - 1 ; i >= 0 ; i--)
					{
						Forum f = forumList[i] as Forum;
						if(f != null)
						{
							if(f.SectionID == 0 || f.ForumType == ForumType.Deleted || f.ForumType == ForumType.Reporting)
								forumList.RemoveAt(i);
						}
					}

					//should add a new GetForums method to specify permissions instead of doing this twice!
					forumList = Sections.FilterByAccessControl(forumList,Permission.Read);

					// Only append this restriction if the user actually has access to any forums
					if(forumList.Count > 0) 
					{
						whereClause.Append(" AND T.SectionID IN (");

						for (int i = 0; i < forumList.Count; i++) 
						{
							// SectionIDs are greater then 0, as filtered above

							if ( (i + 1) < forumList.Count) 
							{
								whereClause.Append( ((Forum) forumList[i]).SectionID + ", ");
							} 
							else 
							{
								whereClause.Append( ((Forum) forumList[i]).SectionID );
							}
						}
						whereClause.Append(")");
					}
					else
					{
						// User has access to nothing
						return threadSet;
					}
				} 
				else 
				{
					whereClause.Append(" AND T.SectionID = 0 AND P.UserID = ");
					whereClause.Append(user.UserID);
					whereClause.Append(" AND P.ThreadID = T.ThreadID ");
					fromClause.Append(", " + this.databaseOwner + ".cs_PrivateMessages P ");
				}
				#endregion

				#region PostMedia
				if (query.PostMedia != PostMediaType.Empty)
				{
					whereClause.AppendFormat(" AND T.ThreadID IN (SELECT P.ThreadID FROM cs_Posts P WHERE ThreadID = T.ThreadID AND (P.PostMedia & {0} = {0}) )", (int)query.PostMedia);
				}
				#endregion

				#region Constrain Date
				whereClause.Append(" AND ThreadDate >= '");
				whereClause.Append( query.ThreadsNewerThan.ToString( System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat.SortableDateTimePattern ));
				whereClause.Append("' ");
				#endregion

				#region Constain Approval
				whereClause.Append(" AND IsApproved = 1");
				#endregion

				#region Unread
				//TODO: See if we can clean this up and remove the inner select
				if(query.UnReadOnly && !user.IsAnonymous)
				{
					whereClause.AppendFormat(" AND (Select Convert(bit,Count(*)) FROM {0}.cs_vw_HasReadForum where ((ThreadID is null and MarkReadAfter > T.ThreadID and LastActivity >= T.ThreadDate) or (MarkReadAfter is null and ThreadID = T.ThreadID)) and UserID = {1} and SectionID = T.SectionID) = 0 ",this.databaseOwner,user.UserID);
				}
				#endregion

				#region Unanswered topics
				if (query.UnAnsweredOnly) 
				{
					whereClause.Append(" AND TotalReplies = 0 AND IsLocked = 0");
				}
				#endregion

				#region Users filter
				if (query.UserFilter != ThreadUsersFilter.All) 
				{

					if ((query.UserFilter == ThreadUsersFilter.HideTopicsParticipatedIn) || (query.UserFilter == ThreadUsersFilter.HideTopicsNotParticipatedIn)) 
					{

						whereClause.Append(" AND ");
						whereClause.Append(user.UserID);

						if (query.UserFilter == ThreadUsersFilter.HideTopicsNotParticipatedIn)
							whereClause.Append(" NOT");

						whereClause.Append(" IN (SELECT UserID FROM " + this.databaseOwner + ".cs_Posts P WHERE P.ThreadID = T.ThreadID)");

					} 
					else 
					{

						if (query.UserFilter == ThreadUsersFilter.HideTopicsByNonAnonymousUsers)
							whereClause.Append(" AND 0 NOT");
						else
							whereClause.Append(" AND 0");

						whereClause.Append(" IN (SELECT UserID FROM " + this.databaseOwner + ".cs_Posts P WHERE ThreadID = T.ThreadID AND P.UserID = 0)");
					}
				}
				#endregion

				#region Order By and Active Topics
				if (query.ActiveTopics) 
				{
					whereClause.Append(" AND IsLocked = 0");
				}

				switch (query.SortBy) 
				{
					case SortThreadsBy.LastPost:
						// SMW: Removed IsSticky as a sort condidtion
						// We should always be ordering by the date only, otherwise a post will always be stick!
						//
						if (query.Order == CommunityServer.Components.SortOrder.Ascending) 
						{
							if (query.IgnoreStickyPosts || query.ActiveTopics || query.UnAnsweredOnly || query.UnReadOnly || myForums)
								orderClause.Append("ThreadDate");
							else
								orderClause.Append(" StickyDate");
						} 
						else 
						{
                            if (query.IgnoreStickyPosts || query.ActiveTopics || query.UnAnsweredOnly || query.UnReadOnly || myForums)
								orderClause.Append("ThreadDate DESC");
							else
								orderClause.Append(" StickyDate DESC");
						}
						break;

					case SortThreadsBy.TotalRatings:
						if (query.Order == CommunityServer.Components.SortOrder.Ascending)
							orderClause.Append("TotalRatings");
						else
							orderClause.Append("TotalRatings DESC");
						break;
	        
					case SortThreadsBy.TotalReplies:
						if (query.Order == CommunityServer.Components.SortOrder.Ascending)
							orderClause.Append("TotalReplies");
						else
							orderClause.Append("TotalReplies DESC");
						break;

					case SortThreadsBy.ThreadAuthor:
						if (query.Order == CommunityServer.Components.SortOrder.Ascending)
							orderClause.Append("PostAuthor DESC");
						else
							orderClause.Append("PostAuthor");
						break;

					case SortThreadsBy.TotalViews:
						if (query.Order == CommunityServer.Components.SortOrder.Ascending)
							orderClause.Append("TotalViews");
						else
							orderClause.Append("TotalViews DESC");
						break;
					case SortThreadsBy.FirstPost:
						if (query.Order == CommunityServer.Components.SortOrder.Ascending)
							orderClause.Append("PostDate");
						else
							orderClause.Append("PostDate DESC");
						break;
				}
				#endregion

				// Build the SQL statements
				sqlCountSelect.Append(fromClause.ToString());
				sqlCountSelect.Append(whereClause.ToString());

				sqlPopulateSelect.Append(fromClause.ToString());
				sqlPopulateSelect.Append(whereClause.ToString());
				sqlPopulateSelect.Append(orderClause.ToString());

				// Add Parameters to SPROC
				//
				command.Parameters.Add("@SectionID", SqlDbType.Int).Value = query.ForumID;
				command.Parameters.Add("@PageIndex", SqlDbType.Int, 4).Value = query.PageIndex;
				command.Parameters.Add("@PageSize", SqlDbType.Int, 4).Value = query.PageSize;
				command.Parameters.Add("@sqlCount", SqlDbType.NText).Value = sqlCountSelect.ToString();
				command.Parameters.Add("@sqlPopulate", SqlDbType.NText).Value = sqlPopulateSelect.ToString();
				command.Parameters.Add("@UserID", SqlDbType.Int).Value = user.UserID;
				command.Parameters.Add("@ReturnRecordCount", SqlDbType.Bit).Value = true;
				command.Parameters.Add("@IncludeCategories", SqlDbType.Bit).Value = query.IncludeCategories;
				command.Parameters.Add(this.SettingsIDParameter());

				Thread newThread = null;
				// Execute the command
				connection.Open();
                
				using(SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection))
				{

					// Populate the ThreadSet
					//
					while (dr.Read()) 
					{

						// Add threads
						//
						if (query.ForumID == 0)
							newThread =  PopulatePrivateMessageFromIDataReader (dr);
						else
							newThread =  PopulateThreadFromIDataReader(dr);


						newThread.User = CommonDataProvider.cs_PopulateUserFromIDataReader(dr);
						threadSet.Threads.Add(newThread);

					}

					if (query.IncludeCategories)
					{
						dr.NextResult();

						Hashtable categoryLookupTable = new Hashtable();

						while(dr.Read()) 
						{
							int postID = (int) dr["PostID"];

							if (categoryLookupTable[postID] == null) 
							{
								categoryLookupTable[postID] = new ArrayList();
							}

							((ArrayList) categoryLookupTable[postID]).Add(dr["Name"]);
						}

						// Map categories to the threads
						//
						foreach (Thread thread in threadSet.Threads) 
						{
							if (categoryLookupTable.ContainsKey(thread.PostID))
								thread.Categories = (string[]) ((ArrayList) categoryLookupTable[thread.PostID]).ToArray(typeof(string));
						}
					}

					// Do we need to return record count?
					//               

					dr.NextResult();

					dr.Read();

					// Read the total records
					//
					threadSet.TotalRecords = (int) dr[0];

                    

					// Get the recipients if this is a request for
					// the private message list
					if ((query.ForumID == 0) && (dr.NextResult()) ) 
					{
						Hashtable recipientsLookupTable = new Hashtable();

						while(dr.Read()) 
						{
							int threadID = (int) dr["ThreadID"];

							if (recipientsLookupTable[threadID] == null) 
							{
								recipientsLookupTable[threadID] = new ArrayList();
							}

							((ArrayList) recipientsLookupTable[threadID]).Add(CommonDataProvider.cs_PopulateUserFromIDataReader(dr) );
						}

						// Map recipients to the threads
						//
						foreach (PrivateMessage thread in threadSet.Threads) 
						{
							thread.Recipients = (ArrayList) recipientsLookupTable[thread.ThreadID];
						}

					}


					dr.Close();
				}
				connection.Close();

				return threadSet;
			}
		}

        #endregion

		#region Threads Read
		public override HybridDictionary GetThreadsRead (int sectionID, int userID) {

			HybridDictionary threadsRead = new HybridDictionary();

			using(SqlConnection myConnection = GetSqlConnection()) {
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_forums_threads_ThreadsRead", myConnection);
				SqlDataReader reader;
				myCommand.CommandType = CommandType.StoredProcedure;
				myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = sectionID;
				myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;
				myCommand.Parameters.Add(this.SettingsIDParameter());
                
				myConnection.Open();
                using(reader = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {

                    // Have we marked all read?
                    reader.Read();
                    threadsRead.Add("ReadAfter", reader["ThreadID"]);

                    // Get the threads read for this section
                    reader.NextResult();
                    while (reader.Read()) 
                    {
                        threadsRead.Add( reader["ThreadID"], reader["ThreadID"]);
                    }
                    reader.Close();
                }

				myConnection.Close();

				return threadsRead;
			}		
		}
		#endregion

		public override PostSet SearchReindexPosts (int setsize, int settingsID) 
		{
			ForumThreadQuery query = new ForumThreadQuery();
			query.FirstPageOnly = true;
			query.PageSize = setsize;
			query.IncludeCategories = true;
			query.IncludePageIndex = true;
			query.SortBy = SortThreadsBy.LastPost;
			query.SortOrder = CommunityServer.Components.SortOrder.Descending;
		
			ThreadSet ts = SearchIndexPosts(settingsID, query);

			PostSet postSet = new PostSet();
			foreach(Post fp in ts.Threads)
				postSet.Posts.Add(fp); 

			return postSet;
		}

		public override ThreadSet SearchIndexPosts (int setsize, ForumThreadQuery query)
		{
			using( SqlConnection connection = GetSqlConnection() ) 
			{
				using(SqlCommand command = new SqlCommand(databaseOwner + ".[cs_shared_Threads_GetThreadSet]", connection))
				{
					command.CommandType = CommandType.StoredProcedure;

					command.Parameters.Add("@SectionID", SqlDbType.Int).Value = query.SectionID;
					command.Parameters.Add("@PageIndex", SqlDbType.Int, 4).Value = query.PageIndex;
					command.Parameters.Add("@PageSize", SqlDbType.Int, 4).Value = SQLHelper.GetSafeSqlInt(query.PageSize) ;
					//command.Parameters.Add("@sqlPopulate", SqlDbType.NText).Value = SqlGenerator.BuildBlogThreadQuery(query,databaseOwner);

					//we are cheating using the blog thread query to get forum posts, so we need to tweak 
					//the index query a bit more than normal

					string sqlindex = new ForumThreadQueryBuilder(query,databaseOwner).BuildQuery();

                    sqlindex = sqlindex.Replace("where ", "where P.IsIndexed=0 and S.IsSearchable=1 and ");
					command.Parameters.Add("@sqlPopulate", SqlDbType.NText).Value = sqlindex;

					command.Parameters.Add("@UserID", SqlDbType.Int).Value = query.UserID;
					command.Parameters.Add("@IncludeCategories", SqlDbType.Bit).Value = query.IncludeCategories;
					command.Parameters.Add("@IncludePageIndex", SqlDbType.Bit).Value = query.IncludePageIndex;
					command.Parameters.Add("@TotalRecords", SqlDbType.Int).Direction = ParameterDirection.Output;
					command.Parameters.Add(SettingsIDParameter());

					ThreadSet ts = new ThreadSet();

					// Execute the command
					
					//If we want to display the list of categories for the post, we will need to 
					//use a dataset so we can join the child records
					if(query.IncludeCategories)
					{
						DataSet ds = new DataSet();
						SqlDataAdapter da = new SqlDataAdapter(command);

						//wait as long as possible to open the conn
						connection.Open();
						da.Fill(ds);
						connection.Close();

						//keep a referece to the relation since it is used in the GetChildRows look up anyway
						DataRelation relation = new DataRelation("Categories",ds.Tables[0].Columns["PostID"],ds.Tables[1].Columns["PostID"],false);
						ds.Relations.Add(relation);
                        
						DataRowCollection posts = ds.Tables[0].Rows;
						foreach(DataRow dr in posts)
						{
							ts.Threads.Add(PopulatePostContentFromDataRow(dr,relation));
						}
   
						if(query.IncludePageIndex)
						{
							DataRowCollection pages = ds.Tables[2].Rows;
							foreach(DataRow dr in pages)
								ts.PageIndex.Add(dr[0], dr[1]);
						}
						
						ds.Clear();
						ds.Dispose();

						ts.TotalRecords = (int) command.Parameters["@TotalRecords"].Value;

					}
					else
					{
						//No categories needed, so we can use a datareader.

						connection.Open();
						using(SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection))
						{
						
							while(dr.Read())
							{
								ts.Threads.Add(PopulatePostEntryFromIDataReader(dr,true));
							}

							if(query.IncludePageIndex)
							{
								dr.NextResult();
								while(dr.Read())
									ts.PageIndex.Add(dr[0], dr[1]);
							}

							dr.Close();
							connection.Close();
							ts.TotalRecords = (int) command.Parameters["@TotalRecords"].Value;
						}
					}

					return ts;

				}
			}
		}

	
	    /// <summary>
        /// Returns count of all posts in system
        /// </summary>
        /// <returns></returns>
        public override int GetTotalPostCount() 
        {
            int totalPostCount = 0;

            // Create Instance of Connection and Command Object
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_GetTotalPostCount", myConnection);

                // Mark the Command as a SPROC
                myCommand.CommandType = CommandType.StoredProcedure;

                myCommand.Parameters.Add(SettingsIDParameter());

                // Execute the command
                myConnection.Open();
                using(SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection|CommandBehavior.SingleRow))
                {

                    if(dr.Read())
                        totalPostCount = (int) dr[0];

                    dr.Close();
                }
                myConnection.Close();

                return totalPostCount;
            }
		}
	
		/// <summary>
		/// Returns the thread page number for the supplied post based on current paging settings
		/// </summary>
		/// <returns>The thread page number</returns>
		public override int GetPageIndex(int postID, int pageSize, int sortBy, int sortOrder)
		{
			int pageIndex = 0;

			// Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Post_GetPageIndex", myConnection);

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				myCommand.Parameters.Add("@PostID", SqlDbType.Int).Value = postID;
				myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
				myCommand.Parameters.Add("@SortBy", SqlDbType.Int).Value = sortBy;
				myCommand.Parameters.Add("@SortOrder", SqlDbType.Int).Value = sortOrder;
				myCommand.Parameters.Add(SettingsIDParameter());

				// Execute the command
				myConnection.Open();
				using(SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection|CommandBehavior.SingleRow))
				{

					if(dr.Read())
						pageIndex = (int) dr[0];

					dr.Close();
				}
				myConnection.Close();

				return pageIndex;
			}
		}

        /****************************************************************
        // MarkPostAsRead
        //
        /// <summary>
        /// Flags a post a 'read' in the database
        /// </summary>
        //
        ****************************************************************/
        public override void MarkPostAsRead(int postID, string username) 
        {
            // Create Instance of Connection and Command Object
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_MarkPostAsRead", myConnection);

                // Mark the Command as a SPROC
                myCommand.CommandType = CommandType.StoredProcedure;

                // Pass sproc parameters
                myCommand.Parameters.Add("@PostID", SqlDbType.SmallInt).Value = postID;
                myCommand.Parameters.Add("@UserName", SqlDbType.NVarChar, 50).Value = username;
                myCommand.Parameters.Add(this.SettingsIDParameter());

                // Execute the command
                myConnection.Open();
                myCommand.ExecuteNonQuery();
                myConnection.Close();
            }
        }


        #region TopN Posts
        /****************************************************************
        // GetTopNPopularPosts
        //
        /// <summary>
        /// TODO
        /// </summary>
        //
        ****************************************************************/
        public override PostSet GetTopNPopularPosts(string username, int postCount, int days) 
        {
            return GetTopNPosts(username, postCount, days, "TotalViews");
        }
        
        /****************************************************************
        // GetTopNPopularPosts
        //
        /// <summary>
        /// ToDO
        /// </summary>
        //
        ****************************************************************/
        public override PostSet GetTopNNewPosts(string username, int postCount) 
        {
            return GetTopNPosts(username, postCount, 0, "ThreadDate");
        }
        
        /****************************************************************
        // GetTopNPopularPosts
        //
        /// <summary>
        /// TODO
        /// </summary>
        //
        ****************************************************************/
        private PostSet GetTopNPosts(string username, int postCount, int days, string sort) 
        {
            PostSet postSet = new PostSet();

            using(SqlConnection myConnection = GetSqlConnection()) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".forums_GetTopNPosts", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@UserName", SqlDbType.NVarChar, 50).Value = username;
                myCommand.Parameters.Add("@SortType", SqlDbType.NVarChar, 50).Value = sort;
                myCommand.Parameters.Add("@PostCount", SqlDbType.Int, 4).Value = postCount;
                myCommand.Parameters.Add("@DaysToCount", SqlDbType.Int, 4).Value = days;
                
                myConnection.Open();
                using(SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection|CommandBehavior.SingleResult)) 
                {
                    
                    while(dr.Read())
                        postSet.Posts.Add( PopulatePostFromIDataReader(dr) );

                    dr.Close();

                }
            

                // Close the connection
                myConnection.Close();
                return postSet;
            }
        }

        #endregion

        #region Forums
        public override Hashtable GetForums() 
        {
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Sections_Get", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;
                Hashtable forums = new Hashtable();

                // Add Parameters to SPROC
                myCommand.Parameters.Add(this.SettingsIDParameter());
                //myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;
                myCommand.Parameters.Add("@ApplicationType", SqlDbType.TinyInt).Value = ApplicationType.Forum;
				myCommand.Parameters.Add("@AllowInactive", SqlDbType.Bit).Value = true;

                // Execute the command
                myConnection.Open();
                using(SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {

                    // Get the requested forums
                    //
                    while (dr.Read()) 
                    {

                        Forum f = PopulateForumFromIDataReader(dr);

                        // add all forums into the Hashtable
                        //
                        forums.Add(f.SectionID, f);

                    }

                    // Loop back through and link up any sub forums
                    // now that the Hashtable is fully populated.
                    // (5/24/2004 fix for subforums with parentid > it's forumid)
                    //
                    foreach (DictionaryEntry di in forums) 
                    {
					
                        // assign a forum to the forumID
                        //
                        Forum f = (Forum) di.Value;		

                        if (f.ParentID > 0)
                            ((Forum) forums[f.ParentID]).Sections.Add(f);
                    }

                    // Get the permissions
                    //
                    if (dr.NextResult()) 
                    {

                        while (dr.Read()) 
                        {
                    
                            // Get the forum id
                            //
                            int forumID = (int) dr["SectionID"];

                            ForumPermission fp = new ForumPermission();
                            CommonDataProvider.PopulatePermissionFromIDataReader( fp, dr);

                            // Are we applying this to a particular forum?
                            if (forums[forumID] != null)  
                            {
                                Forum f = forums[forumID] as Forum;

                                fp.SectionID = forumID;
                                f.PermissionSet.Add( fp.Name, fp );

//                                if( !fp.View || !fp.Read )
//                                    f.IsPrivate = true;
                            } 
                            else if (forumID == -1) 
                            {

                                // Apply the permission to all forums
                                foreach(Forum forum in forums.Values) 
                                {

                                    fp.SectionID = forum.SectionID;

                                    // Merge the permission
                                    if (forum.PermissionSet[fp.Name] != null) 
                                    {
                                        /*
                                                                            ForumPermission inheritedPermission = forum.PermissionSet[fp.Name] as ForumPermission;

                                                                            inheritedPermission.Merge(fp);
                                        */
                                    } 
                                    else 
                                    {
                                        forum.PermissionSet.Add( fp.Name, fp );
                                    }

//                                    if( !fp.View || !fp.Read )
//                                        forum.IsPrivate = true;
                                }
                            }


                        }

                    }
            
                    // Done with the reader and the connection
                    //
                    dr.Close();
                }
                myConnection.Close();

                return forums;
            }
        }


        #endregion

        #region Forum Data
        /// <summary>
        /// Returns a Forum object with information on a particular forum.
        /// </summary>
        /// <returns>A Forum object.</returns>
        /// <remarks>If a ForumID is passed in that is NOT found in the database, a ForumNotFoundException
        /// exception is thrown.</remarks>
        public override int GetForumIDByPostID(int postID) 
        {

            // Create Instance of Connection and Command Object
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Section_GetSectionIDByPostID", myConnection);
                int forumID = 0;

                // Mark the Command as a SPROC
                myCommand.CommandType = CommandType.StoredProcedure;

                // Add Parameters to SPROC
                myCommand.Parameters.Add(this.SettingsIDParameter());
                myCommand.Parameters.Add("@PostID", SqlDbType.Int).Value = postID;


                // Execute the command
                myConnection.Open();
                object obj = myCommand.ExecuteScalar();
                myConnection.Close();
                if(obj != null)
                   forumID = (int)obj;

                return forumID;
            }
        }

        /****************************************************************
        // MarkAllForumsRead
        //
        /// <summary>
        /// Marks all forums as read
        /// </summary>
        //
        *****************************************************************/
        public override void MarkAllForumsRead(int userID, int forumGroupID, int forumID, bool markAllThreadsRead) 
        {

            // Create Instance of Connection and Command Object
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Section_MarkRead", myConnection);

                // Mark the Command as a SPROC
                myCommand.CommandType = CommandType.StoredProcedure;

                // Add Parameters to SPROC
                myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;
                myCommand.Parameters.Add("@GroupID", SqlDbType.Int).Value = forumGroupID;
                myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = forumID;
                myCommand.Parameters.Add("@MarkAllThreadsRead", SqlDbType.Bit).Value = markAllThreadsRead;
                myCommand.Parameters.Add(this.SettingsIDParameter());

                // Open the connection
                myConnection.Open();
                myCommand.ExecuteNonQuery();
                myConnection.Close();
            }
        }


        #endregion

        #region #### Private Messages ####
        public override void CreatePrivateMessage(ArrayList users, int threadID) 
        {

            // Create Instance of Connection and Command Object
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_PrivateMessages_CreateDelete", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                myCommand.Parameters.Add("@Action", SqlDbType.Bit).Value = DataProviderAction.Create;
                myCommand.Parameters.Add("@UserID", SqlDbType.Int);
                myCommand.Parameters.Add("@ThreadID", SqlDbType.Int);
                myCommand.Parameters.Add(this.SettingsIDParameter());

                // Open the connection
                myConnection.Open();

                // Add multiple times
                //
                foreach (User user in users) 
                {
                    myCommand.Parameters["@UserID"].Value = user.UserID;
                    myCommand.Parameters["@ThreadID"].Value = threadID;


                    myCommand.ExecuteNonQuery();

                }

                myConnection.Close();
            }
        }

        public override void DeletePrivateMessage(int userID, ArrayList deleteList) 
        {

            // Create Instance of Connection and Command Object
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_PrivateMessages_CreateDelete", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                myCommand.Parameters.Add("@Action", SqlDbType.Int).Value = DataProviderAction.Delete;
                myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;
                myCommand.Parameters.Add("@ThreadID", SqlDbType.Int);
                myCommand.Parameters.Add(this.SettingsIDParameter());

                // Open the connection
                myConnection.Open();

                // Add multiple times
                //
                foreach (int threadID in deleteList) 
                {
                    myCommand.Parameters["@ThreadID"].Value = threadID;

                    myCommand.ExecuteNonQuery();
                }
                
                // Close connection
                //
                myConnection.Close();
            }
        }

        public override HybridDictionary GetPrivateMessageRecipients(int threadID) 
        {
            HybridDictionary list = new HybridDictionary();

            // Create Instance of Connection and Command Object
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_PrivateMessages_Recipients", myConnection);
                SqlDataReader reader;
                myCommand.CommandType = CommandType.StoredProcedure;

                myCommand.Parameters.Add("@ThreadID", SqlDbType.Int).Value = threadID;
                myCommand.Parameters.Add(this.SettingsIDParameter());

                // Open the connection
                myConnection.Open();

                using(reader = myCommand.ExecuteReader(CommandBehavior.CloseConnection|CommandBehavior.SingleResult))
                {

                    while (reader.Read()) 
                    {

                        if (list[ (int) reader["UserID"] ] == null)
                            list.Add( (int) reader["UserID"], reader["Username"].ToString() );
                    }
                    reader.Close();
                }

                myConnection.Close();
            }

            return list;
        }

        #endregion

        #region #####Forums Permissions #####

        public override ArrayList GetForumPermissions(int sectionID) 
        {

            // Create Instance of Connection and Command Object
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Section_Permissions_Get", myConnection);
                Hashtable forumPermissions = new Hashtable();
                SqlDataReader reader;

                // Mark the Command as a SPROC
                myCommand.CommandType = CommandType.StoredProcedure;

                // Add Parameters to SPROC
                myCommand.Parameters.Add("@SectionID", SqlDbType.Int, 4).Value = sectionID;
                myCommand.Parameters.Add("@ApplicationType", SqlDbType.TinyInt).Value = ApplicationType.Forum;
                myCommand.Parameters.Add(this.SettingsIDParameter());
                

                myConnection.Open();

                using(reader = myCommand.ExecuteReader(CommandBehavior.CloseConnection|CommandBehavior.SingleResult))
                {
                    while(reader.Read()) 
                    {
                        ForumPermission fp = new ForumPermission();
                        CommonDataProvider.PopulatePermissionFromIDataReader (fp,reader);

                        // Merge the permission
                        if (forumPermissions[fp.RoleID] != null) 
                        {

                        } 
                        else 
                        {
                            forumPermissions.Add(fp.RoleID, fp);
                        }

                    }

                    reader.Close();
                }

                myConnection.Close();


                return new ArrayList(forumPermissions.Values);
            }
        }

        #endregion

        #region #### RSS ####
        public override void RssPingback (Hashtable pingbackList) 
        {
            // Create Instance of Connection and Command Object
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Section_RssPingback_Update", myConnection);
                //            int totalAnonymousUsers = 0;

                // Mark the Command as a SPROC
                myCommand.CommandType = CommandType.StoredProcedure;

                // Set parameters
                //
                myCommand.Parameters.Add("@SectionID", SqlDbType.Char, 36);
                myCommand.Parameters.Add("@Pingback", SqlDbType.NVarChar, 512);
                myCommand.Parameters.Add("@Count", SqlDbType.Int);

                // Open the connection
                //
                myConnection.Open();

                foreach (string key in pingbackList.Keys) 
                {
                
                    myCommand.Parameters["@SectionID"].Value = ((RssPingback) pingbackList[key]).ForumID;
                    myCommand.Parameters["@Pingback"].Value = ((RssPingback) pingbackList[key]).Url;
                    myCommand.Parameters["@Count"].Value = ((RssPingback) pingbackList[key]).Count;
                    myCommand.Parameters.Add(this.SettingsIDParameter());

                    myCommand.ExecuteNonQuery();

                }

                // Close the connection
                //
                myConnection.Close();
            }
        }
        #endregion


        #region #### Moderation ####
        public override ArrayList GetForumsToModerate(int userID, ApplicationType applicationType) 
        {
            // Create Instance of Connection and Command Object
            //
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Moderate_Forums", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;
                ArrayList forums = new ArrayList();

                // Set parameters
                //
                myCommand.Parameters.Add(this.SettingsIDParameter());
				myCommand.Parameters.Add("@ApplicationType", SqlDbType.SmallInt).Value = applicationType;
                myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;

                // Execute the command
                //
                myConnection.Open();
                using(SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection|CommandBehavior.SingleResult))
                {

                    // Get the results
                    //
                    while (dr.Read())
                        forums.Add( PopulateForumFromIDataReader(dr) );
                    
                    dr.Close();
                }
                myConnection.Close();

                return forums;
            }
        }

        /// <summary>
        /// Approves a particular post that is waiting to be moderated.
        /// </summary>
        /// <param name="postID">The ID of the post to approve.</param>
        /// <returns>A boolean indicating if the post has already been approved.</returns>
        /// <remarks>Keep in mind that multiple moderators may be working on approving/moving/editing/deleting
        /// posts at the same time.  Hence, these moderation functions may not perform the desired task.
        /// For example, if one opts to delete a post that has already been approved, the deletion will
        /// fail.</remarks>
        public override bool ApprovePost(int postID, int userIDApprovedBy) 
        {

            // Create Instance of Connection and Command Object
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Moderate_ApprovePost", myConnection);

                // Mark the Command as a SPROC
                myCommand.CommandType = CommandType.StoredProcedure;

                // Add Parameters to SPROC
                myCommand.Parameters.Add("@PostID", SqlDbType.Int).Value = postID;
                myCommand.Parameters.Add("@ApprovedBy", SqlDbType.Int).Value = userIDApprovedBy;
                myCommand.Parameters.Add(this.SettingsIDParameter());

                // Execute the command
                myConnection.Open();
                int iResult = Convert.ToInt32(myCommand.ExecuteScalar().ToString());
                myConnection.Close();

                return iResult == 1;        // was the post previously approved?
            }
        }

        public override bool CheckIfUserIsModerator (int userID, int forumID) 
        {

            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Moderate_CheckUser", myConnection);
                bool canModerate = false;

                // Mark the Command as a SPROC
                myCommand.CommandType = CommandType.StoredProcedure;

                // Add Parameters to SPROC
                myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;
                myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = forumID;
                myCommand.Parameters.Add(this.SettingsIDParameter());

                // Execute the command
                myConnection.Open();
                using(SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection|CommandBehavior.SingleRow))
                {

                    // Check to see if the user can moderate
                    if (dr.Read())
                        if ( (int) dr[0] > 0 )
                            canModerate = true;

                    dr.Close();
                }
                myConnection.Close();
            
                return canModerate;        
            }
        }



        public override PostSet GetPostsToModerate(int forumID, int pageIndex, int pageSize, int sortBy, int sortOrder, int userID, bool returnRecordCount) 
        {
            // Create Instance of Connection and Command Object
            //
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_forums_Moderate_PostSet", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;
                PostSet postSet = new PostSet();

                // Set parameters
                //
                myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = forumID;
                myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex;
                myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
                myCommand.Parameters.Add("@SortBy", SqlDbType.Int).Value = sortBy;
                myCommand.Parameters.Add("@SortOrder", SqlDbType.Int).Value = sortOrder;
                myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;
                myCommand.Parameters.Add("@ReturnRecordCount", SqlDbType.Bit).Value = returnRecordCount;
                myCommand.Parameters.Add(this.SettingsIDParameter());

                // Execute the command
                //
                myConnection.Open();
                using(SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {

                    // Get the results
                    //
                    while (dr.Read())
                        postSet.Posts.Add( PopulatePostFromIDataReader(dr) );

                    // Are we expecting more results?
                    //
                    if ((returnRecordCount) && (dr.NextResult()) )
                        postSet.TotalRecords = (int) dr[0];

                    dr.Close();
                }
                myConnection.Close();

                return postSet;
            }
        }

        public override void TogglePostSettings (ModeratePostSetting setting, ForumPost post, int moderatorID) 
        {
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Post_ToggleSettings", myConnection);

                // Mark the Command as a SPROC
                myCommand.CommandType = CommandType.StoredProcedure;

                // Add Parameters to SPROC
                myCommand.Parameters.Add("@PostID", SqlDbType.Int).Value = post.PostID;
                myCommand.Parameters.Add("@IsAnnouncement", SqlDbType.Bit).Value = false;
                myCommand.Parameters.Add("@IsLocked", SqlDbType.Bit).Value = post.IsLocked;
                myCommand.Parameters.Add("@ModeratorID", SqlDbType.Int).Value = moderatorID;
				myCommand.Parameters.Add(this.SettingsIDParameter());

                switch (setting) 
                {

                    case ModeratePostSetting.ToggleAnnouncement:
                        /*
                        if (post.IsAnnouncement)
                            myCommand.Parameters["@IsAnnouncement"].Value = false;
                        else
                            myCommand.Parameters["@IsAnnouncement"].Value = true;
                            */
                        break;

                    case ModeratePostSetting.ToggleLock:
                        if (post.IsLocked)
                            myCommand.Parameters["@IsLocked"].Value = false;
                        else
                            myCommand.Parameters["@IsLocked"].Value = true;
                        break;

                }

                // Execute the command
                myConnection.Open();
                myCommand.ExecuteNonQuery();
                myConnection.Close();
            }
        }

        public override void ToggleUserSettings (ModerateUserSetting setting, User user, int moderatorID) 
        {

            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_User_ToggleSettings", myConnection);

                // Mark the Command as a SPROC
                myCommand.CommandType = CommandType.StoredProcedure;

                // Add Parameters to SPROC
                myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = user.UserID;
                myCommand.Parameters.Add("@ModerationLevel", SqlDbType.Int).Value = (int) user.ModerationLevel;
                myCommand.Parameters.Add("@UserAccountStatus", SqlDbType.Int).Value = (int) user.AccountStatus;
                myCommand.Parameters.Add("@IsAvatarApproved", SqlDbType.Bit).Value = user.IsAvatarApproved;
                myCommand.Parameters.Add("@ForceLogin", SqlDbType.Bit).Value = user.ForceLogin;
                myCommand.Parameters.Add("@ModeratorID", SqlDbType.Int).Value = moderatorID;
                myCommand.Parameters.Add(this.SettingsIDParameter());

                switch (setting) 
                {
                    case ModerateUserSetting.ToggleForceLogin:
                        if (user.ForceLogin)
                            myCommand.Parameters["@ForceLogin"].Value = false;
                        else
                            myCommand.Parameters["@ForceLogin"].Value = true;
                        break;

                    case ModerateUserSetting.ToggleApproval:
                        if (user.AccountStatus == UserAccountStatus.Approved)
                            myCommand.Parameters["@UserAccountStatus"].Value = UserAccountStatus.ApprovalPending;
                        else
                            myCommand.Parameters["@UserAccountStatus"].Value = UserAccountStatus.Approved;
                        break;

                    case ModerateUserSetting.ToggleModerate:
                        if (user.ModerationLevel == ModerationLevel.Unmoderated)
                            myCommand.Parameters["@ModerationLevel"].Value = ModerationLevel.Moderated;
                        else
                            myCommand.Parameters["@ModerationLevel"].Value = ModerationLevel.Unmoderated;
                        break;

                    case ModerateUserSetting.ToggleAvatarApproved:
                        if (user.IsAvatarApproved)
                            myCommand.Parameters["@IsAvatarApproved"].Value = false;
                        else
                            myCommand.Parameters["@IsAvatarApproved"].Value = true;
                        break;

                }

                // Execute the command
                myConnection.Open();
                myCommand.ExecuteNonQuery();
                myConnection.Close();
            }
        }


        /****************************************************************
        // GetQueueStatus
        //
        ****************************************************************/
        public override ModerationQueueStatus GetQueueStatus(int sectionID, string username) 
        {
            ModerationQueueStatus moderationQueue = new ModerationQueueStatus();

            // Create Instance of Connection and Command Object
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_forums_GetUnmoderatedPostStatus", myConnection);
                SqlDataReader reader;

                // Mark the Command as a SPROC
                myCommand.CommandType = CommandType.StoredProcedure;

                // Add Parameters to SPROC
                myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = sectionID;
                myCommand.Parameters.Add("@UserName", SqlDbType.NVarChar, 50).Value = username;
				myCommand.Parameters.Add(this.SettingsIDParameter());

                // Execute the command
                myConnection.Open();

                using(reader = myCommand.ExecuteReader(CommandBehavior.CloseConnection|CommandBehavior.SingleResult))
                {
                    while (reader.Read()) 
                    {
                        moderationQueue.AgeInMinutes = (int) reader["OldestPostAgeInMinutes"];
                        moderationQueue.Count = (int) reader["TotalPostsInModerationQueue"];
                    }

                    reader.Close();
                }
                myConnection.Close();

                return moderationQueue;
            }
        }
        #endregion


        #region Moderation

        public override void ThreadSplit(int postID, int moveToForumID, int splitByUserID) 
        {

            // Splits a single thread into two threads
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Moderate_Thread_Split", myConnection);

                // Mark the Command as a SPROC
                myCommand.CommandType = CommandType.StoredProcedure;

                // Add Parameters to SPROC
                myCommand.Parameters.Add("@PostID", SqlDbType.Int, 4).Value = postID;
                myCommand.Parameters.Add("@MoveToForum", SqlDbType.Int, 4).Value = moveToForumID;
                myCommand.Parameters.Add("@SplitBy", SqlDbType.Int, 4).Value = splitByUserID;
                myCommand.Parameters.Add(this.SettingsIDParameter());

                // Execute the command
                myConnection.Open();
                myCommand.ExecuteNonQuery();
                myConnection.Close();
            }
        }

        public override void ThreadJoin(int parentThreadID, int childThreadID, int joinedByUserID) 
        {

            // Joins two thread into one threads
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Moderate_Thread_Merge", myConnection);

                // Mark the Command as a SPROC
                myCommand.CommandType = CommandType.StoredProcedure;

                // Add Parameters to SPROC
                myCommand.Parameters.Add("@ParentThreadID", SqlDbType.Int, 4).Value = parentThreadID;
                myCommand.Parameters.Add("@ChildThreadID", SqlDbType.Int, 4).Value = childThreadID;
                myCommand.Parameters.Add("@JoinBy", SqlDbType.Int, 4).Value = joinedByUserID;
                myCommand.Parameters.Add(this.SettingsIDParameter());

                // Execute the command
                myConnection.Open();
                myCommand.ExecuteNonQuery();
                myConnection.Close();
            }
        }

        public override ArrayList GetForumModeratorRoles (int forumID) 
        {

            // Joins two thread into one threads
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Moderate_Forum_Roles", myConnection);
                SqlDataReader reader;
                ArrayList roles = new ArrayList();

                // Mark the Command as a SPROC
                myCommand.CommandType = CommandType.StoredProcedure;

                // Add Parameters to SPROC
                myCommand.Parameters.Add("@SectionID", SqlDbType.Int, 4).Value = forumID;
                myCommand.Parameters.Add(this.SettingsIDParameter());

                // Execute the command
                myConnection.Open();

                using(reader = myCommand.ExecuteReader(CommandBehavior.CloseConnection|CommandBehavior.SingleResult))
                {
                    while (reader.Read())
                        roles.Add(CommonDataProvider.PopulateRoleFromIDataReader( reader ));

                    reader.Close();
                }
                myConnection.Close();

                return roles;
            }
        }

        /// <summary>
        /// Deletes a post and records the action in the moderator log.
        /// </summary>
        /// <param name="postID">The post to delete</param>
        /// <param name="deletedBy">The moderator who is deleting the post.</param>
        /// <param name="reason">The reason why the post is removed.</param>
        /// <remarks>This function doesn't care if the post is approved or not, it will get removed.</remarks>
        public override void ModeratorDeletePost(int postID, int deletedBy, string reason, bool deleteChildPosts) 
        {
            // Create Instance of Connection and Command Object
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Moderate_DeletePost", myConnection);

                // Mark the Command as a SPROC
                myCommand.CommandType = CommandType.StoredProcedure;

                // Add Parameters to SPROC
                myCommand.Parameters.Add("@PostID", SqlDbType.Int, 4).Value = postID;
                myCommand.Parameters.Add("@DeletedBy", SqlDbType.Int, 4).Value = deletedBy;
                myCommand.Parameters.Add("@Reason", SqlDbType.NVarChar, 1024).Value = reason;
                myCommand.Parameters.Add("@DeleteChildPosts", SqlDbType.Bit).Value = deleteChildPosts;
                myCommand.Parameters.Add(this.SettingsIDParameter());

                // Execute the command
                myConnection.Open();
                myCommand.ExecuteNonQuery();
                myConnection.Close();
            }
        }

        
        /// <summary>
        /// Determines if a user can edit a particular post.
        /// </summary>
        /// <param name="Username">The name of the User.</param>
        /// <param name="PostID">The Post the User wants to edit.</param>
        /// <returns>A boolean value - True if the user can edit the Post, False otherwise.</returns>
        /// <remarks>An Administrator can edit any post.  Moderators may edit posts from forums that they
        /// have moderation rights to and that are awaiting approval.</remarks>
        public override  bool CanEditPost(String Username, int PostID) 
        {
            // Create Instance of Connection and Command Object
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".forums_CanEditPost", myConnection);

                // Mark the Command as a SPROC
                myCommand.CommandType = CommandType.StoredProcedure;

                // Add Parameters to SPROC
                SqlParameter parameterPostID = new SqlParameter("@PostID", SqlDbType.Int, 4);
                parameterPostID.Value = PostID;
                myCommand.Parameters.Add(parameterPostID);

                SqlParameter parameterUsername = new SqlParameter("@UserName", SqlDbType.NVarChar, 50);
                parameterUsername.Value = Username;
                myCommand.Parameters.Add(parameterUsername);

                // Execute the command
                myConnection.Open();
                int iResponse = Convert.ToInt32(myCommand.ExecuteScalar().ToString());
                myConnection.Close();
            
                return iResponse == 1;
            }
        }

        /// <summary>
        /// Moves a post from one Forum to another.
        /// </summary>
        /// <param name="postID">The post to move.</param>
        /// <param name="moveToForumID">The forum to move the post to.</param>
        /// <param name="movedBy">The user who is moving the post.</param>
        /// <returns>A MovedPostStatus enumeration value that indicates the status of the attempted move.
        /// This enumeration has four values: NotMoved, MovedButNotApproved, MovedAndApproved and MovedAlreadyApproved.</returns>
        /// <remarks>A value of NotMoved means the post was not moved; a value of MovedButNotApproved indicates that the post has been moved to a new
        /// forum, but the user moving the post was NOT a moderator for the forum it was moved to, hence
        /// the moved post is still waiting to be approved; a value of MovedAndApproved indicates that the
        /// moderator moved to post to a forum he moderates, hence the post is automatically approved; a value
        /// of MovedAlreadyApproved indicates the already approved post was moved.</remarks>
        public override MovedPostStatus MovePost(int postID, int moveToForumID, int movedBy) 
        {

            // moves a post to a specified forum
            // Create Instance of Connection and Command Object
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Moderate_Post_Move", myConnection);

                // Mark the Command as a SPROC
                myCommand.CommandType = CommandType.StoredProcedure;

                // Add Parameters to SPROC
                myCommand.Parameters.Add("@PostID", SqlDbType.Int, 4).Value = postID;
                myCommand.Parameters.Add("@MoveToSectionID", SqlDbType.Int, 4).Value = moveToForumID;
                myCommand.Parameters.Add("@MovedBy", SqlDbType.Int, 4).Value = movedBy;
                myCommand.Parameters.Add(this.SettingsIDParameter());

                // Execute the command
                myConnection.Open();
                int iStatus = Convert.ToInt32(myCommand.ExecuteScalar().ToString());
                myConnection.Close();

                // Determine the status of the moved post
                switch (iStatus) 
                {
                    case 0:
                        return MovedPostStatus.NotMoved;
                    
                    case 1:
                        return MovedPostStatus.MovedButNotApproved;

                    case 3:
                        return MovedPostStatus.MovedAlreadyApproved;

                    case 2:
                    default:
                        return MovedPostStatus.MovedAndApproved;
                }
            }
        }

		public override void UpdatePostParent(int postID, int parentID, int movedBy) 
		{

			// moves a post to a specified forum
			// Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Moderate_Post_UpdateParent", myConnection);

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@PostID", SqlDbType.Int, 4).Value = postID;
				myCommand.Parameters.Add("@ParentID", SqlDbType.Int, 4).Value = parentID;
				myCommand.Parameters.Add("@MovedBy", SqlDbType.Int, 4).Value = movedBy;
				myCommand.Parameters.Add(this.SettingsIDParameter());

				// Execute the command
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				myConnection.Close();
			}
		}


        // **********************************************************************
        /// <summary>
        /// Given a username, returns a boolean indicating whether or not the user has
        /// posts awaiting moderation.
        /// </summary>
        // **********************************************************************
        public override bool UserHasPostsAwaitingModeration(String username) 
        {
//
//            //TODO 
//
//            // Create Instance of Connection and Command Object
//            using( SqlConnection myConnection = GetSqlConnection() ) 
//            {
//                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_UserHasPostsAwaitingModeration", myConnection);
//
//                // Mark the Command as a SPROC
//                myCommand.CommandType = CommandType.StoredProcedure;
//
//                // Add Parameters to SPROC
//                myCommand.Parameters.Add("@UserName", SqlDbType.NVarChar, 50).Value = username;
//                myCommand.Parameters.Add(this.SettingsIDParameter());
//
//                // Open the database connection and execute the command
//                SqlDataReader dr;
//
//                myConnection.Open();
//                using(dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
//                {
//                    dr.Close();
//                }
//
//                // create a String array from the data
//                ///ArrayList userRoles = new ArrayList();
//
//
//
//                // Return the String array of roles
//
//            }
                            return false;
        }


        /// <summary>
        /// Retrieves a list of the Forums moderated by a particular user.
        /// </summary>
        /// <param name="user">The User whose list of moderated forums we are interested in.</param>
        /// <returns>A ArrayList.</returns>
//        public override  ArrayList GetForumsModeratedByUser(String Username) 
//        {
//            // Create Instance of Connection and Command Object
//            using( SqlConnection myConnection = GetSqlConnection() ) 
//            {
//                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_forums_GetForumsModeratedByUser", myConnection);
//
//                // Mark the Command as a SPROC
//                myCommand.CommandType = CommandType.StoredProcedure;
//
//                // Add Parameters to SPROC
//                SqlParameter parameterUsername = new SqlParameter("@UserName", SqlDbType.NVarChar, 50);
//                parameterUsername.Value = Username;
//                myCommand.Parameters.Add(parameterUsername);
//
//                myCommand.Parameters.Add(this.SettingsIDParameter());
//
//                // Execute the command
//                myConnection.Open();
//                ArrayList forums = new ArrayList();
//                using(SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection|CommandBehavior.SingleResult))
//                {
//                    ModeratedForum forum;
//                    while (dr.Read()) 
//                    {
//                        forum = new ModeratedForum();
//                        forum.SectionID = Convert.ToInt32(dr["SectionID"]);
//                        forum.Name = Convert.ToString(dr["ForumName"]);
//                        forum.DateCreated = Convert.ToDateTime(dr["DateCreated"]);
//                        forum.EmailNotification = Convert.ToBoolean(dr["EmailNotification"]);
//
//                        forums.Add(forum);
//                    }
//                    dr.Close();
//                }
//                myConnection.Close();
//
//                return forums;
//            }
//        }


       #endregion
	}
}
